<?php
	class CustomerPaymentController extends Controller{
		private $result = array("actionStatus"=>0,"actionMessage"=>"","model"=>array(),"actionError"=>array());	
		public $mainMenu = "Finance";
		public $subMenu = "";
		public $title = "";
		
		public function init() {
						
		}
		
		public function actionIndex(){
			$data= Array();
			
			$this->layout ="//layouts/ly_blank";
			$this->render('index',$data);
		}
		public function actionDetailList(){
			$start_date = Yii::app()->request->getParam("start_date","01-Jan-2000");			
			$start_date = date_format(date_create_from_format( "d-M-Y", $start_date),"Y-m-d");
			$end_date=Yii::app()->request->getParam("end_date","01-Jan-2000");
			$end_date = date_format(date_create_from_format( "d-M-Y", $end_date),"Y-m-d");
			
			$arrPayment = MCustomerHpayment::model()->findAll("payment_date >= :startDate and payment_date <= :endDate",array(
					":startDate"=>$start_date,
					":endDate"=>$end_date
			));
			$model = Array();
			
			foreach ($arrPayment as $payment){
				$tmp = Array();
				$tmp["id"] = $payment->id;
				$tmp["payment_no"] = $payment->payment_no;
				$tmp["payment_date"] = $payment->payment_date;
				$tmp["customer_id"] = $payment->customer_id;
				$tmp["customer_name"] = $payment->customer->name;
				$tmp["receive_acc_id"] = $payment->receive_acc_id;
				$tmp["ttl_payment"] = $payment->ttl_payment;
				$tmp["notes"] = $payment->notes;
				$model[] = $tmp;				
			}
			
			$this->result["actionStatus"]=0;
			$this->result["model"]=$model;			
			$this->returnJSON($this->result);			
		}
		
		public function actionDetailListDetail(){
			$start_date = Yii::app()->request->getParam("start_date","01-Jan-2000");
			$start_date = date_format(date_create_from_format( "d-M-Y", $start_date),"Y-m-d");
			$end_date=Yii::app()->request->getParam("end_date","01-Jan-2000");
			$end_date = date_format(date_create_from_format( "d-M-Y", $end_date),"Y-m-d");
			
			$criteria = new CDbCriteria();			
			$arrPayment = MCustomerDpayment::model()->with(
					array("customerPayment"=>array("alias"=>"a"))
				)->findAll("a.payment_date >= :startDate and a.payment_date <= :endDate",array(
					":startDate"=>$start_date,
					":endDate"=>$end_date
					
			));
				
			
			
			$model = Array();
				
			foreach ($arrPayment as $payment){
				$tmp = Array();
				$tmp["id"] = $payment->id;
				$tmp["payment_no"] = $payment->customerPayment->payment_no;
				$tmp["payment_date"] = $payment->customerPayment->payment_date;
				$tmp["customer_id"] = $payment->customerPayment->customer_id;
				$tmp["customer_name"] = $payment->customerPayment->customer->name;
				$tmp["receive_acc_id"] = $payment->customerPayment->receive_acc_id;
				$tmp["invoice_id"] = $payment->customer_invoice_id;
				$tmp["invoice_no"] = $payment->customerInvoice->invoice_no;				
				$tmp["ttl_payment"] = $payment->total_payment;
				
				$tmp["notes"] = $payment->notes;
				$tmp["is_active"] = $payment->customerPayment->is_active;
				$model[] = $tmp;
			}
				
			$this->result["actionStatus"]=0;
			$this->result["model"]=$model;
			$this->returnJSON($this->result);
		}
		public function actionInvoiceList(){
			$customer_id = Yii::app()->request->getParam("customer_id","");
			$arrInvoice = MCustomerHinvoice::model()->findAll("customer_id = :custId and (ttl_paid + ttl_cn) < (ttl_invoice-ttl_disc) and is_active = 1",array(":custId"=>$customer_id));
			$model = array();
			foreach ($arrInvoice as $inv){
				$tmp = array();
				$tmp["id"] = $inv->id;
				$tmp["invoice_no"] = $inv->invoice_no;
				$tmp["invoice_date"] = $inv->invoice_date;
				$tmp["notes"] = $inv->notes;
				$tmp["total"] = ($inv->ttl_invoice - $inv->ttl_disc) - ($inv->ttl_paid + $inv->ttl_cn);
				$tmp["payment"] = $tmp["total"];				
				$model[] = $tmp;
			}
			$this->result["actionStatus"] =0;
			$this->result["model"] =$model;		
			$this->returnJSON($this->result);
			
		}
		
		public function actionForm(){
			$invoice_id = Yii::app()->request->getParam("invoice_id",0);
			$op = Yii::app()->request->getParam("op","");
			$id = Yii::app()->request->getParam("id",0);
			$payment_no = "";
			$payment_date = date("d-M-Y");
			$customer_id ="";
			$customer_name="";
			$notes ="";
			$arrInvoice="[]";
			
			if($op == "add"){
				$payment_no = AutoNumber::getNewCustomerPaymentNo();				
			}
			
			if($invoice_id != ""){
				$hInvoice = MCustomerHinvoice::model()->findByPK($invoice_id);
				$customer_id = $hInvoice->customer_id;
				$customer_name = $hInvoice->customer->name;
				$arrInvoice = MCustomerHinvoice::model()->findAll("customer_id = :customerId and (ttl_paid + ttl_cn) < (ttl_invoice -ttl_disc) and is_active = 1",array(":customerId"=>$customer_id));
				$arrTmp = array();
				foreach ($arrInvoice as $inv){
					$tmp = array();
					$tmp["id"] = $inv->id;
					$tmp["invoice_no"] = $inv->invoice_no;
					$tmp["invoice_date"] = $inv->invoice_date;
					$tmp["notes"] = $inv->notes;
					$tmp["total"] = ($inv->ttl_invoice - $inv->ttl_disc) - ($inv->ttl_paid + $inv->ttl_cn);
					$tmp["payment"] = $tmp["total"];
					$arrTmp[] = $tmp;
				}
				$arrInvoice = json_encode($arrTmp);
			}
			
			$groupReceiveId = AppSetting::getValue("penerimaan_acc_id");
			$groupReceive = MCoa::model()->find("id =:accId",array(":accId"=>$groupReceiveId));
			$arrReceiveAcc = MCoa::model()->findAll("acc_code like :groupCode and is_active = 1 and is_leaf = 1",array(":groupCode"=>$groupReceive->acc_code."%" ));
			$receive_acc_id ="";
			if ($arrReceiveAcc != null){
				$tmp = $arrReceiveAcc[0];
				$receive_acc_id =$tmp->id;
			}
			
			$arrCustomer = MCustomer::model()->findAll("is_active = 1");
			$acCustomer = array();
			foreach ($arrCustomer as $customer){
				$tmp = array();
				$tmp["label"] = $customer->name;
				$tmp["value"] = $customer->name;
				$tmp["id"] = $customer->id;
				$acCustomer[] = $tmp;
			}
			$data["op"]=$op;
			$data["id"]=$id;
			$data["payment_no"]=$payment_no;
			$data["payment_date"]=$payment_date;
			$data["arrReceiveAcc"] = $arrReceiveAcc;
			$data["acCustomer"]=CJSON::encode($acCustomer);			
			$data["customer_name"]=$customer_name;
			$data["customer_id"]=$customer_id;
			$data["receive_acc_id"]=$receive_acc_id;
			$data["notes"]=$notes ;
			$data["arrInvoice"] = $arrInvoice;
			$data["invoice_id"] = $invoice_id;
			
			$this->layout ="//layouts/ly_blank";
			$this->render('form',$data);
		}
		
		public function actionEdit(){
			$errorMessage =Yii::t("label","Pembayaran gagal di simpan !");
			$op = Yii::app()->request->getParam("op","");
			$id = Yii::app()->request->getParam("id",0);
			
			if ($op == "add"){
				$payment_date =Yii::app()->request->getParam("payment_date","");
				if ($payment_date == ""){
					$this->pageError(Yii::t("label","data payment date tidak valid"));
				}else{
					$payment_date = date_format(date_create_from_format( "d-M-Y", $payment_date),"Y-m-d");
				}
				$customer_id = Yii::app()->request->getParam("customer_id","");
				$receive_acc_id = Yii::app()->request->getParam("receive_acc_id","");
				$ttl_payment = Yii::app()->request->getParam("ttl_payment",0);
				$ttl_payment = Utils::parseCurrency($ttl_payment);
				$notes = Yii::app()->request->getParam("notes","");
				$strArrPaidInvoice = Yii::app()->request->getParam("arrPaidInvoice","[]");
				$arrPaidInvoice = json_decode($strArrPaidInvoice);
				$conn = Yii::app()->db;
				$transaction = $conn->beginTransaction();				
				try{
					$payment_no = AutoNumber::getNewCustomerPaymentNo();
					
					//Jurnal Payment
					$tmp = Jurnal::createHeader("Payment $payment_no ", $payment_date);
					$jurnalId = $tmp["id"];
					
					//payment header
					$hPayment = new MCustomerHpayment();					
					$hPayment->payment_no = $payment_no;
					$hPayment->payment_date =$payment_date;
					$hPayment->customer_id = $customer_id;
					$hPayment->receive_acc_id = $receive_acc_id;
					$hPayment->ttl_payment = $ttl_payment;
					$hPayment->notes= $notes;
					$hPayment->jurnal_id = $jurnalId;
					$hPayment->input_date_time= date("Y-m-d H:i:s");
					$loginUser = LoginUser::getUser();
					$hPayment->user_id= $loginUser->id;
					$hPayment->is_active = 1;
					if(!$hPayment->save()){
						throw new Exception($hPayment->getErrors);
					}
					
					$ttlPayment =0;
					//payment Detail
					foreach ($arrPaidInvoice as $paidInvoice){
						$dPayment = new MCustomerDpayment();
						$dPayment->customer_payment_id = $hPayment->id;
						$dPayment->customer_invoice_id = $paidInvoice->id;
						$dPayment->total_invoice = $paidInvoice->total;
						$dPayment->total_payment = $paidInvoice->payment;
						$dPayment->notes = $paidInvoice->notes;
						
						$ttlPayment += $paidInvoice->payment;
						
						if(!$dPayment->save()){
							throw new Exception($dPayment->getErrors);
						}
						//Update Invoice header
						$hInvoice = MCustomerHinvoice::model()->findByPK($paidInvoice->id);
						if ($hInvoice == null){
							throw new Exception("Invalid invoice ID");
						}
						
						$hInvoice->ttl_paid = $hInvoice->ttl_paid + $paidInvoice->payment;
						if(!$hInvoice->save()){
							throw new Exception(json_encode($hInvoice->getErrors()));
							
						}
						//Jurnal detail for piutang account position Credit 
						$tmp = Jurnal::createDetail($jurnalId, $hInvoice->piutang_acc_id, "", "C",$hInvoice->ttl_paid);	
					}
					//create detail Jurnal					
					$tmp = Jurnal::createDetail($jurnalId, $receive_acc_id, "", "D", $ttlPayment);
				}catch (Exception $e){
					$transaction->rollback();
					$this->pageError($errorMessage, $e->getMessage(),$e);					
				}				
				$transaction->commit();				
				$this->redirect(array('keuangan/tu/customerPayment/paymentReceipt','id'=>$hPayment->id));				
			}else if($op == "delete"){
				$errorMessage = Yii::t("dialog","Data pembayaran gagal di hapus!");
				try{
					$customerPayment = MCustomerHpayment::model()->find("id =:id",array(":id"=>$id));
					if ($customerPayment == null){
						$errorMessage = Yii::t("dialog","Data Pembayaran tidak di temukan !");
						throw new Exception("Invalid payment id");
					}
					$conn = Yii::app()->db;
					$transaction = $conn->beginTransaction();
					$jurnalId = $customerPayment->jurnal_id;
					$payment_no = $customerPayment->payment_no;

					// delete detail jurnal & detail Payment
					Jurnal::deleteDetailJurnal($jurnalId);
					$arrDPayment = MCustomerDpayment::model()->findAll("customer_payment_id = ".$customerPayment->id);
					foreach ($arrDPayment as $dPayment){
						//update invoice
						$customerInv = MCustomerHinvoice::model()->find("id = " .$dPayment->customer_invoice_id);
						if ($customerInv != null){
							$customerInv->ttl_paid = $customerInv->ttl_paid - $dPayment->total_payment;
							if (!$customerInv->save()){
								throw new Exception(json_encode($customerInv->getErrors()));
							}														
						}
					}
					$ssql = "delete from m_customer_dpayment where customer_payment_id = ".$customerPayment->id;
					$cmd = $conn->createCommand($ssql);
					$cmd->execute();
					
					// delete header payment & header jurnal
					if(!$customerPayment->delete()){
						throw new Exception(json_encode($customerPayment->getErrors()));
					}
					Jurnal::deleteHeaderJurnal($jurnalId,$conn);
					$transaction->commit();
					$successMessage = Yii::t("dialog",'Pembayaran $payment_no telah di hapus !');
					$successMessage = str_replace('$payment_no', $payment_no, $successMessage);						
					$this->result["actionStatus"] = 0;
					$this->result["actionMessage"] = $successMessage;  
					$this->result["actionError"] = "";
					$this->returnJSON($this->result);					
				}catch (Exception $e){
					$transaction->rollback();
					$this->result["actionStatus"] = 1;
					$this->result["actionMessage"] = Yii::t("dialog",$errorMessage);
					$this->result["actionError"] = $e->getMessage();
					$this->returnJSON($this->result);
				}
			}						
		}
		
		public function actionPaymentReceipt(){
			$errorMessage = Yii::t("label","Data pembayaran tidak di temukan !");
			$id = Yii::app()->request->getParam("id","");
			$hPayment = MCustomerHpayment::model()->find("id =:paymentId and is_active = 1",array(":paymentId"=>$id));
			
			if ($hPayment == null){
				$this->pageError($errorMessage,"Payment id not found");
			}
			
			$arrDPayment = MCustomerDpayment::model()->findAll("customer_payment_id =:paymentId",array(":paymentId"=>$id));
			$data["id"] = $id;
			$data["hPayment"] = $hPayment;
			$data["arrDPayment"] = $arrDPayment;
			
			$this->layout ="//layouts/ly_blank";
			$this->render('paymentReceipt',$data);			
		}
		
		public function actionPaymentReceiptAsExcel(){
				
			//prepare data
			$errorMessage = Yii::t("label","Data pembayaran tidak di temukan !");
			$id = Yii::app()->request->getParam("id","");
			$hPayment = MCustomerHpayment::model()->find("id =:paymentId and is_active = 1",array(":paymentId"=>$id));
			
			if ($hPayment == null){
				$this->pageError($errorMessage,"Payment id not found");
			}
			
			$arrDPayment = MCustomerDpayment::model()->findAll("customer_payment_id =:paymentId",array(":paymentId"=>$id));
			$data["hPayment"] = $hPayment;
			$data["arrDPayment"] = $arrDPayment;
				
			//prepare Excel Object
			Yii::import('ext.phpexcel.XPHPExcel');
				
			//$objPHPExcel= XPHPExcel::createPHPExcel();
				
				
			$filetemplate = "customerPayment_paymentReceipt.xlsx";
			//$filetemplate = dirname(__FILE__) . DIRECTORY_SEPARATOR . "..". DIRECTORY_SEPARATOR ."exceltemplate". DIRECTORY_SEPARATOR ."keuangan". DIRECTORY_SEPARATOR ."tu".DIRECTORY_SEPARATOR .$filetemplate;
			$filetemplate =  Yii::app()->basePath .DIRECTORY_SEPARATOR . "exceltemplate". DIRECTORY_SEPARATOR ."keuangan". DIRECTORY_SEPARATOR ."tu".DIRECTORY_SEPARATOR .$filetemplate;
			//$filetemplate =  "E:\\test.xlsx";
			//echo $filetemplate ;
				
			$objPHPExcel = XPHPExcel::createPHPExcel();
			$objReader = PHPExcel_IOFactory::createReader('Excel2007');
			$objPHPExcel = $objReader->load($filetemplate);
				
			$objPHPExcel->getProperties()->setCreator("Raba Komunikatama")
			->setLastModifiedBy("Raba Komunikatama")
			->setTitle("Office 2007 XLSX Stundent Payment Receipt")
			->setSubject("Office 2007 XLSX Stundent Payment Receipt")
			->setDescription("Stundent Payment Receipt for Office 2007 XLSX, generated by Raba Komunikatama.")
			->setKeywords("office 2007 openxml php")
			->setCategory("");
		
		
			$objPHPExcel->setActiveSheetIndex(0);
			$myWorkSheet = $objPHPExcel->getActiveSheet();
			// Add some data Header
			$myWorkSheet->setCellValue('C3', $hPayment->customer->name)
			->setCellValue('C4', $hPayment->receiveAcc->name)
			->setCellValue('C5', $hPayment->notes)
			->setCellValue('C6', $hPayment->user->user_name)
			->setCellValue('F3', $hPayment->payment_no)
			->setCellValue('F4', date_format(date_create_from_format( "Y-m-d", $hPayment->payment_date),"d-M-Y"));
				
			// Add detail data
			$grandTtl =0;
			$jmlDetail = sizeof($arrDPayment);
				
			//$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A8:G8'),'A'. $jmlDetail + 8 .':A' . $jmlDetail + 8);
			//$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A8:G8'),'A9:G13');
				
			$activeRow =8;
			foreach($arrDPayment as $dPayment){
		
				$myWorkSheet->mergeCells('A'.$activeRow.':B'.$activeRow);
		
				//rata Kanan
				$myWorkSheet->getStyle("E$activeRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				$myWorkSheet->getStyle("F$activeRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
				$myWorkSheet->getStyle("G$activeRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		
				//format money
				$myWorkSheet->getStyle("E$activeRow:G$activeRow")->getNumberFormat()->setFormatCode("#,##0");
				
				//warp text
				$objPHPExcel->getActiveSheet()->getStyle("D$activeRow")->getAlignment()->setWrapText(true);
		
				$grandTtl += $dPayment->total_payment;
				$balance = $dPayment->total_invoice - $dPayment->total_payment;
				$myWorkSheet->setCellValue('A' . $activeRow,  $dPayment->customerInvoice->invoice_no)
				->setCellValue('C' . $activeRow,  date_format(date_create_from_format( "Y-m-d", $dPayment->customerInvoice->invoice_date),"d-M-Y"))
				->setCellValue('D' . $activeRow,  $dPayment->notes)
				->setCellValue('E' . $activeRow,  $dPayment->total_invoice)
				->setCellValue('F' . $activeRow,  $dPayment->total_payment)
				->setCellValue('G' . $activeRow,  $balance);
		
				//Utils::formatCurrency($balance));
				$activeRow++;
				$myWorkSheet->insertNewRowBefore($activeRow,1);
		
				//$objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('A8:G8'),'A'.$activeRow.':G'.$activeRow);
			}
				
			//footer
			$myWorkSheet->mergeCells('A'.$activeRow.':D'.$activeRow);
			$myWorkSheet->setCellValue('A' . $activeRow,  "##". Utils::terbilang($grandTtl) ."##");
			$myWorkSheet->mergeCells('E'.$activeRow.':G'.$activeRow);
				
			$myWorkSheet->setCellValue('E' . $activeRow,  "Total Payment: Rp. ". Utils::formatCurrency($grandTtl));
			$myWorkSheet->getStyle("E$activeRow")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
			// Rename worksheet
			$objPHPExcel->getActiveSheet()->setTitle('Student Payment Receipt');
		
			// Set active sheet index to the first sheet, so Excel opens this as the first sheet
			$objPHPExcel->setActiveSheetIndex(0);
				
			// Redirect output to a client’s web browser (Excel5)
			header('Content-Type: application/vnd.ms-excel');
			header('Content-Disposition: attachment;filename="CustomerPaymentReceipt_'. $hPayment->payment_no . '.xls"');
			header('Cache-Control: max-age=0');
			// If you're serving to IE 9, then the following may be needed
			header('Cache-Control: max-age=1');
		
			// If you're serving to IE over SSL, then the following may be needed
			header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
			header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
			header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
			header ('Pragma: public'); // HTTP/1.0
		
			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output');
			Yii::app()->end();
			exit;
				
				
		
			$this->layout ="//layouts/ly_blank";
			$this->render('paymentReceipt',$data);
		}
		protected function beforeRender($view){
			if ($this->subMenu != ""){
				$this->title = $this->mainMenu . " - " .$this->subMenu;
			}
			return true;
		}
		
	}
?>